package com.system.demo.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.system.demo.entity.database.Leave;
import com.system.demo.entity.mailPage.LeaveVO;
import com.system.demo.entity.mailPage.LeaveWorkVO;
import com.system.demo.mapper.dynamicSqlProvider.LeaveSqlProvider;
import org.apache.ibatis.annotations.*;


import java.time.LocalDateTime;


@Mapper
public interface LeaveMapper extends BaseMapper<Leave> {


    @Delete("DELETE FROM leave_school WHERE id = #{id} AND uid = #{uid}")
    void deleteById(@Param("id") Integer id, @Param("uid") String uid);

    @Select("SELECT * FROM leave_school WHERE id = #{id}")
    Leave findByApplicationId(@Param("id") Integer id);

//    @Select("SELECT * FROM leave_school WHERE uid = #{uid}")
//    List<Leave> findByUid(@Param("uid") String uid);

    @Update({
            "UPDATE leave_school", // 确保表名正确
            "SET status = #{newStatus},approval_time = #{approvalTime}",
            "WHERE id IN ${idListStr}"
    })
    int batchUpdateStatus(@Param("idListStr") String idListStr, @Param("newStatus") Integer newStatus,@Param("approvalTime") LocalDateTime approvalTime);

    @Update("UPDATE leave_school SET status = #{status},checking_time = #{checkingTime} WHERE id = #{applicationId} AND status = 2")
    int updateLeaveStatus(@Param("applicationId") Integer applicationId, @Param("status") Integer status,@Param("checkingTime") LocalDateTime checkingTime);

    @Select("SELECT student_name " +
            "FROM student " +
            "WHERE uid = #{uid}")
    String findStudentNameByUid(@Param("uid") String uid);

    @Select("SELECT student_class " +
            "FROM student " +
            "WHERE uid = #{uid}")
    String findStudentNameByClass(@Param("uid") String uid);

    @SelectProvider(type = LeaveSqlProvider.class, method = "selectLeaveByConditions")
    Page<Leave> selectLeaveByConditions(Page<Leave> page,
                                        @Param("uid") String uid,
                                        @Param("userName") String userName,
                                        @Param("startTime") String startTime,
                                        @Param("endTime") String endTime,
                                        @Param("status") Integer status,
                                        @Param("stuClass") String stuClass);

    /**
     * 更新请假申请记录
     * @param leave 要更新的Leave对象
     * @return 受影响的行数
     */
    @Update("UPDATE leave_school SET " +
            "reason = #{reason}, " +
            "position = #{position}, " +
            "leaving_time = #{leavingTime}, " +
            "backing_time = #{backingTime}, " +
            "number = #{number}, " +
            "application_time = #{applicationTime},"+
            "approval_time = #{approvalTime},"+
            "checking_time = #{checkingTime},"+
            "approved_time = #{approvedTime},"+
            "url = #{url} " +
            "WHERE id = #{id}")
    int updateLeave(Leave leave);

    @Update({
            "UPDATE leave_school", // 确保表名正确
            "SET status = #{newStatus},approved_time = #{approvedTime}",
            "WHERE id IN ${idListStr}"
    })
    int backupdateStatus(@Param("idListStr") String idListStr, @Param("newStatus") Integer newStatus,@Param("approvedTime") LocalDateTime approvedTime);


    @Select("<script>" +
            "SELECT " +
            "  COALESCE(p.count, 0) AS count, " +
            "  COALESCE(p.leaving, 0) AS leaving, " +
            "  COALESCE(p.verifiedReturn, 0) AS verifiedReturn, " +
            "  COALESCE(p.overnightStay, 0) AS overnightStay " +
            "FROM (" +
            "  SELECT " +
            "    COUNT(*) AS count, " +
            "    COUNT(CASE WHEN status IN (2, 3) THEN 1 END) AS leaving, " +
            "    COUNT(CASE WHEN status = 4 THEN 1 END) AS verifiedReturn, " +
            "    COUNT(CASE WHEN status = 5 THEN 1 END) AS overnightStay " +
            "  FROM " +
            "    leave_school " +
            "  WHERE " +
            "    uid = #{uid}" +
            ") p " +
            "RIGHT JOIN (SELECT 1) AS dummy ON TRUE" +
            "</script>")
    LeaveVO selectStatusCountsByUser(@Param("uid") String uid);



    @Select("<script>" +
            "SELECT " +
            "  COUNT(*) AS count, " +
            "  COALESCE(SUM(CASE WHEN status IN (2, 3) THEN 1 ELSE 0 END), 0) AS leaving, " +
            "  COALESCE(SUM(CASE WHEN status = 4 THEN 1 ELSE 0 END), 0) AS verifiedReturn, " +
            "  COALESCE(SUM(CASE WHEN status = 5 THEN 1 ELSE 0 END), 0) AS overnightStay " +
            "FROM leave_school " +
            "</script>")
    LeaveVO selectStatusCountsByAdmin();

    @Select("<script>" +
            "SELECT " +
            "  COALESCE(SUM(CASE WHEN status = 0 THEN 1 ELSE 0 END), 0) AS pendingReview, " +
            "  COALESCE(SUM(CASE WHEN status = 3 THEN 1 ELSE 0 END), 0) AS toBeRecognized " +
            "FROM leave_school " +
            "</script>")
    LeaveWorkVO selectStatusCounts();
}
